"""
Case Type   : Compatibility
Case Name   : 验证执行语句为select语句;部分测试点合理报错
Create At   : 2024/10/16
Owner       : liu-tong-8848
Description :
              1.创建表并插入数据
              2.创建prepare语句;preparable_stmt参数为查询表中数据;
              3.创建prepare语句;preparable_stmt参数为查询系统表;
              4.创建prepare语句;preparable_stmt语句本身报错;
              5.创建prepare语句;preparable_stmt语句语法错误;
              6.创建prepare语句;preparable_stmt参数为select函数;
              7.清理环境
Expect      :
              1.成功
              2.成功
              3.成功
              4.合理报错
              5.合理报错
              6.成功
              7.清理环境成功
History     :
"""

import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class SQL(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbBmode')
        self.db_user_node = Node(node='PrimaryDbBmode')

    def test_add_dimension(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        step1 = '--step1:创建表并插入数据;expect:成功'
        self.log.info(step1)
        sql = "drop table if exists t_prepare_005a; create table t_prepare_005a(c1 int,c2 text); " \
              + "insert into t_prepare_005a values(1,'a');"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step1)

        step2 = '--step2:创建prepare语句;preparable_stmt参数为查询表中数据;expect:成功'
        self.log.info(step2)
        sql = "prepare test_prepare_005a from 'select * from t_prepare_005a'; execute test_prepare_005a;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step2)

        step3 = '--step3:创建prepare语句;preparable_stmt参数为查询系统表;expect:成功'
        self.log.info(step3)
        sql = "prepare test_prepare_005b from ' select name from pg_settings limit 1'; execute test_prepare_005b;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step3)

        step4 = '--step4:创建prepare语句;preparable_stmt语句本身报错;expect:合理报错'
        self.log.info(step4)
        sql = "drop table if exists t_prepare_005c; prepare test_prepare_005d from 'select * from t_prepare_005c';"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("ERROR:  relation \"t_prepare_005c\" does not exist", result, "执行失败" + step4)

        step5 = '--step5:创建prepare语句;preparable_stmt语句语法错误;expect:合理报错'
        self.log.info(step5)
        sql = "prepare test_prepare_005d from 'select * from t_prepare_005a having c1=1';"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertIn("ERROR", result, "执行失败" + step5)

        step6 = '--step6:创建prepare语句;preparable_stmt参数为select函数;expect:成功'
        self.log.info(step6)
        sql = "prepare test_prepare_005d from 'select to_base64(1)'; execute test_prepare_005d; " \
              + "deallocate prepare test_prepare_005d; prepare test_prepare_005d from 'select bit_count(1)'; " \
              + "execute test_prepare_005d; deallocate prepare test_prepare_005d; " \
              + "prepare test_prepare_005d from 'select bit_xor(1)'; execute test_prepare_005d; " \
              + "deallocate prepare test_prepare_005d; prepare test_prepare_005d from 'select current_user()'; " \
              + "execute test_prepare_005d; deallocate prepare test_prepare_005d; " \
              + "prepare test_prepare_005d from 'select isnull(1)'; execute test_prepare_005d; " \
              + "deallocate prepare test_prepare_005d; prepare test_prepare_005d from 'select hex(1)'; " \
              + "execute test_prepare_005d; deallocate prepare test_prepare_005d; " \
              + "prepare test_prepare_005d from 'select ifnull(1,1)'; execute test_prepare_005d; " \
              + "deallocate prepare test_prepare_005d;"
        self.log.info(sql)
        result = self.primary_sh.execut_db_sql(sql)
        self.log.info(result)
        self.assertNotIn("ERROR", result, "执行失败" + step6)


    def tearDown(self):
        step7 = 'step7:清理环境 expect:清理环境成功'
        self.log.info(step7)
        clean_sql = "deallocate prepare test_prepare_005a; deallocate prepare test_prepare_005b; " \
                    + "deallocate prepare test_prepare_005c; deallocate prepare test_prepare_005d;"
        self.primary_sh.execut_db_sql(clean_sql)
        self.log.info("prepare语句无法跨会话生效，由于自动化用例会跨会话执行这些语句，所以自动化结束时无需清理")
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
